<?php

namespace app\service;

use app\model\DepartmentModel;
use app\model\TaskModel;
use app\model\TaskWorkOrderModel;
use app\tool\SingletonTrait;
use support\Request;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\db\Query;

class TaskStatisticsService
{
    use SingletonTrait;

    /**
     * 任务统计
     * @param Request $request
     * @param array $param
     * @param string $filterCategory
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function taskStatistics(Request $request, array $param, string $filterCategory): array
    {
        $res = [];
        $queryCodeList = $param['query_code'];
        //把指定code移到最前
        $keyCodeList = array_filter($queryCodeList, fn($code) => in_array($code, ['plan_done', 'plan_delivery', 'in_all_done', 'in_all_delivery']));
        $queryCodeList = array_unique(array_merge($keyCodeList, $queryCodeList));
        $keyCodeList = $param['query_type'] == 'full' ? [] : array_fill_keys($keyCodeList, false);
        $param['tenant_id'] = $request->getTenantId();

        // ·针对范围为指定任务ID的查询
        $pureMode = !empty($param['task_id']);
        // 节点模式
        if ($param['node_mode'] === 'yes') {
            switch ($param['history']) {
                case 'yes':
                case 'no':
                    $param['history'] = 'node_' . $param['history'];
                    break;
                default:
                    $param['history'] = 'node_default';
            }
            $pureMode = true;
        }

        foreach ($queryCodeList as $queryCode) {
            $resRow = [
                'query_code' => $queryCode,
                'data' => null,
            ];
            $queryDbr = self::dealQueryCodeSkip($param, $queryCode, $keyCodeList);
            if (!$queryDbr) {
                switch ($filterCategory) {
                    case "done":
                        $queryDbr = self::getTaskDoneBaseQuery($request, $queryCode, $param, $pureMode);
                        break;
                    case "delivery":
                        $queryDbr = self::getTaskDeliveryBaseQuery($request, $queryCode, $param, $pureMode);
                        break;
                    case "abnormal":
                        $queryDbr = self::getTaskAbnormalBaseQuery($request, $queryCode, $param, $pureMode);
                        break;
                    case "dynamic":
                        $queryDbr = self::getTaskDynamicBaseQuery($request, $queryCode, $param, $pureMode);
                        break;
                }
            }
            $resultEmpty = false;
            if ($queryDbr !== true && !($queryDbr instanceof Query)) {
                $data = is_array($queryDbr) ? null : $queryDbr;
                if ($param['view_type'] == 'aggregate') {
                    $res[$queryCode] = $data;
                } else {
                    $resRow['data'] = $data;
                    $res[] = $resRow;
                }
            } else {
                // ! pureMode
                if ($pureMode && ($queryDbr instanceof Query)) {
                    $queryDbr = $queryDbr->whereFieldRaw("find_in_set(task.id, '" . implode(',', $param['task_id']) . "')", '>=', 1);
                }
                $field = '';
                $data = [];
                switch ($param['view_type']) {
                    case 'aggregate':
                        if ($queryDbr === true) {
                            $res[$queryCode] = 0;
                            break;
                        }
                        $res[$queryCode] = $queryDbr->count('distinct task.id');
                        $resultEmpty = empty($res[$queryCode]);
                        break;
                    case 'task_id_list':
                        if ($queryDbr === true) {
                            $res[$queryCode] = [];
                            break;
                        }
                        $res[$queryCode] = array_map('intval', $queryDbr->column('distinct task.id'));
                        $resultEmpty = empty($res[$queryCode]);
                        break;
                    case 'multiple':
                        // 查询结果按日组织
                        if ($queryDbr === true) {
                            $queryResult = [];
                        } else {
                            $dayKey = 'date';
                            $dateSql = in_array($param['history'], ['yes', 'solid'])
                                ? 'if(task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ', task.plan_end_time, dot.original_plan_time)'
                                : 'task.plan_end_time';
                            $field = 'count(distinct task.id) as count, from_unixtime(' . $dateSql . ', "%Y-%m-%d") as ' . $dayKey;
                            $queryResult = $queryDbr->field($field)
                                ->group('from_unixtime(' . $dateSql . ', "%Y-%m-%d")');
                            $queryResult = $queryResult
                                ->select()->toArray();
                            $resultEmpty = array_sum(array_column($queryResult, 'count')) == 0;
                            $queryResult = array_column($queryResult, null, $dayKey);
                        }
                        foreach (get_month_day_list($param['start_time'], $param['end_time']) as $date) {
                            $data[$date] = $queryResult[$date] ?? [$dayKey => $date];
                        }
                        $data = result_array_construct([
                            'count' => 'intval',
                            $dayKey => 'strval',
                        ], $data);
                        $data = array_values($data);
                        break;
                    default:
                        if (empty($field)) {
                            $data = $queryDbr === true ? 0 : $queryDbr->count('distinct task.id');
                        } else {
                            $data = $queryDbr === true ? [] : $queryDbr->field($field)->select()->toArray();
                        }
                        $resultEmpty = empty($data);
                        break;
                }
                if (!in_array($param['view_type'], ['aggregate', 'task_id_list'])) {
                    $resRow['data'] = $data;
                    $res[] = $resRow;
                }
                if (isset($keyCodeList[$queryCode]) && $resultEmpty) {
                    $keyCodeList[$queryCode] = true;
                }
            }
        }
        // 格式化返回
        if (in_array($param['view_type'], ['aggregate', 'task_id_list'])) {
            return $res;
        }
        return result_array_construct([
            'query_code' => 'strval',
            'data' => '',
        ], $res);
    }

    public function dealQueryCodeSkip($param, $queryCode, $keyCodeList = [])
    {
        // 当key所在结果为空时，rows不查询数据库，直接返回对应格式空值
        $codeSkipList = [
            'plan_done' => [            // 前置条件：计划完成 0
                'in_plan_done',         // 计划内完成
                'in_plan_not_done',     // 计划内未完成
                'in_plan_not_deal',     // 计划内未处理
                'in_plan_not_deal_done',
            ],
            'plan_delivery' => [                // 前置条件：计划交付 0
                'in_plan_delivery',             // 计划内交付
                'in_plan_not_delivery',         // 计划内未交付
                'in_plan_not_deal_delivery',    // 计划内交付未处理
            ],
            'in_all_done' => [        // 前置条件：实际完成 0
                'outside_plan_done',  // 计划外完成**
            ],
            'in_all_delivery' => [              // 前置条件：实际交付 0
                'outside_plan_delivery',        // 计划外交付**
            ],
        ];
        foreach ($keyCodeList as $code => $status) {
            if ($status && in_array($queryCode, $codeSkipList[$code] ?? [])) {
                return true;
            }
        }
        return false;
    }

    /**
     * 过滤: 任务类型、任务状态
     * @param       $queryDbr
     * @param array $param
     * @return void
     */
    public function taskStepTypeFilter(&$queryDbr, array $param)
    {
        if ($queryDbr instanceof Query) {
            // 过滤 任务类型
            switch ($param['query_category'] ?? []) {
                case 'step': // 生产任务
                    $queryDbr = $queryDbr->whereIn('task.step_type', ['step', 'to_do']);
                    break;
                case 'standard': // 标准任务
                    $queryDbr = $queryDbr->whereIn('task.step_type', ['standard_meeting', 'standard_review']);
                    break;
                default:
                    if (empty($param['task_step_type'])) {
                        // 全部任务 需过滤step_category
                        $queryDbr = $queryDbr->whereNotIn('task.step_type', ['step_category', 'standard_make_to_do']);
                    } else {
                        $queryDbr = $queryDbr->whereIn('task.step_type', $param['task_step_type']);
                    }
                    break;
            }
            if (!empty($param['task_status'])) {
                $queryDbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap($param['task_status'], false, 'id') . ')');
            }
        }

    }

    /**
     * 过滤: 任务的用户、部门、环节
     * @param       $queryDbr
     * @param array $param
     * @param array $userIdList
     * @param int $tenantId
     * @return void
     */
    public function taskMyResponsibleFilter(&$queryDbr, array $param, array $userIdList = [], int $tenantId = 0)
    {
        if ($queryDbr instanceof Query) {
            // 过滤我负责的环节
            if (!empty(($param['step_category_id']))) {
                $queryDbr = $queryDbr->join(['task_relation_tag' => 'r_step_category'], "r_step_category.link_module_code IN ('step_category', 'show_step_category') and r_step_category.task_id = task.id")
                    ->whereIn('r_step_category.link_id', $param['step_category_id']);
            }
            // 过滤我负责的部门
            if (!empty($param['department_id'])) {
                $userIdList = DepartmentService::getInstance()->getDepartmentUserIdList($param['department_id']);
            }
            if (empty($param['step_category_id']) || !empty($param['department_id'])) {
                $userIdList = array_filter($userIdList);        // 测试环境出现过 $request->getSaasUserId() 返回 NULL 的情况，过滤掉
                if (empty($userIdList)) {
                    $queryDbr = [];
                    return;
                } else {
                    $queryDbr = $queryDbr->join('task_member', 'task_member.task_id = task.id and task_member.partner in (' . implode(',', $userIdList) . ')');
                }
            }

            $queryDbr = $queryDbr->where('task.tenant_id', $tenantId);
        }
    }

    /**
     * param处理: 上游环节
     * @param $param
     * @return array
     */
    public function upstreamTaskParser($param): array
    {
        $param = query_array_construct([
            'step_category_id' => 'split_array|array_map,intval',    // 环节ID 列表
            'department_id' => 'split_array|array_map,intval',       // 部门ID 列表
        ], $param);
        // 过滤我负责的部门
        $departmentStepCategoryMap = $param['step_category_id'];
        if (!empty($param['department_id'])) {
            $departmentStepCategoryInfo = DepartmentModel::whereIn('id', $param['department_id'])->column('step_category_ids');
            foreach ($departmentStepCategoryInfo as $item) {
                if (!empty($item)) {
                    $keys = explode(',', $item);
                    $departmentStepCategoryMap = array_unique(array_merge($departmentStepCategoryMap, $keys));
                }
            }
            $param['department_id'] = [];
        }
        $param['step_category_id'] = [];
        if (!empty($departmentStepCategoryMap)) {
            $stepCategoryUpstreamDownstreamRelation = StepCategoryService::getInstance()->getStepCategoryUpstreamDownstreamRelation($departmentStepCategoryMap);
            $upstreamStepCategoryIdList = array_unique(array_merge(...array_column($stepCategoryUpstreamDownstreamRelation, 'upstream')));
            if (!empty($upstreamStepCategoryIdList)) {
                $param['step_category_id'] = $upstreamStepCategoryIdList;
            }
        }
        return $param;
    }

    /**
     * @param Request $request
     * @param string $queryCode
     * @param array $param
     * @return TaskModel|array|false
     */
    private function getTaskDoneBaseQuery(Request $request, string $queryCode, array $param, $pureMode = false)
    {
        switch ($queryCode) {
            case 'plan_done': // 计划完成
                $queryDbr = self::taskPlanDoneBaseQuery($param);
                break;
            case 'in_plan_done': // 计划内完成
                $queryDbr = self::taskInPlanDoneBaseQuery($param);
                break;
            case 'outside_plan_done': // 计划外完成
                $queryDbr = self::taskOutsidePlanDoneBaseQuery($param);
                break;
            case 'in_all_done': // 实际完成
                $queryDbr = self::taskInAllDoneBaseQuery($param);
                break;
            case 'in_plan_not_done':// 计划内未完成
                $queryDbr = self::taskInPlanNotDoneBaseQuery($param);
                break;
            case 'in_plan_not_deal':// 计划未处理
            case 'in_plan_not_deal_done':
                $queryDbr = self::taskInPlanNotDealBaseQuery($param);
                break;
            case 'in_plan_need_solution_done': // 无解决方案
                $queryDbr = self::taskInPlanNeedSolutionDoneBaseQuery($param);
                break;
            case 'in_plan_pending': // 计划内处理中
            case 'in_plan_pending_done':
                $queryDbr = self::taskInPlanPendingBaseQuery($param);
                break;
            case 'in_plan_has_solution_done': // 有解决方案
            case 'in_plan_marked': // 计划内已处理
            case 'in_plan_marked_done':
                $queryDbr = self::taskInPlanMarkedBaseQuery($param);
                break;
            case 'in_plan_marked_today': // 计划内标记今日
            case 'in_plan_marked_today_done':
                $queryDbr = self::taskInPlanMarkedTodayBaseQuery($param);
                break;
            case 'in_plan_overdue': // 计划内逾期
            case 'in_plan_overdue_done':
                $queryDbr = self::taskInPlanOverdueBaseQuery($param);
                break;
            case 'in_plan_delay': // 计划内延期
            case 'in_plan_delay_done':
                $queryDbr = self::taskInPlanDelayBaseQuery($param);
                break;
            default:
                $queryDbr = true;
        }
        if (!$pureMode) {
            if ($queryDbr instanceof Query) {
                // TODO - 是否全局
                $userIdList = [$request->getSaasUserId()];
                self::taskStepTypeFilter($queryDbr, $param);
                self::taskMyResponsibleFilter($queryDbr, $param, $userIdList, $request->getTenantId());
            }
        }
        return $queryDbr;
    }

    /**
     * @param Request $request
     * @param string $queryCode
     * @param array $param
     * @return TaskModel|array|false
     */
    private function getTaskDeliveryBaseQuery(Request $request, string $queryCode, array $param, $pureMode = false)
    {
        switch ($queryCode) {
            case 'plan_delivery':
                $queryDbr = self::taskPlanDeliveryBaseQuery($param);
                break;
            case 'in_plan_delivery':
                $queryDbr = self::taskInPlanDeliveryBaseQuery($param);
                break;
            case 'outside_plan_delivery':
                $queryDbr = self::taskOutsidePlanDeliveryBaseQuery($param);
                break;
            case 'in_all_delivery':
                $queryDbr = self::taskInAllDeliveryBaseQuery($param);
                break;
            case 'in_plan_not_delivery':
                $queryDbr = self::taskInPlanNotDeliveryBaseQuery($param);
                break;
            case 'in_plan_not_deal_delivery':
                $queryDbr = self::taskInPlanNotDealDeliveryBaseQuery($param);
                break;
            case 'in_plan_need_solution_delivery': // 无解决方案
                $queryDbr = self::taskInPlanNeedSolutionDeliveryBaseQuery($param);
                break;
            case 'in_plan_pending_delivery':
                $queryDbr = self::taskInPlanPendingDeliveryBaseQuery($param);
                break;
            case 'in_plan_has_solution_delivery': // 有解决方案
            case 'in_plan_marked_delivery':
                $queryDbr = self::taskInPlanMarkedDeliveryBaseQuery($param);
                break;
            case 'in_plan_marked_today_delivery':
                $queryDbr = self::taskInPlanMarkedTodayDeliveryBaseQuery($param);
                break;
            case 'in_plan_overdue_delivery':
                $queryDbr = self::taskInPlanOverdueDeliveryBaseQuery($param);
                break;
            case 'in_plan_delay_delivery':
                $queryDbr = self::taskInPlanDelayDeliveryBaseQuery($param);
                break;
            default:
                $queryDbr = true;
        }
        if (!$pureMode) {
            if ($queryDbr instanceof Query) {
                // TODO - 是否全局
                $userIdList = [$request->getSaasUserId()];
                self::taskStepTypeFilter($queryDbr, $param);
                self::taskMyResponsibleFilter($queryDbr, $param, $userIdList, $request->getTenantId());
            }
        }
        return $queryDbr;
    }

    /**
     * @param Request $request
     * @param string $queryCode
     * @param array $param
     * @return array|mixed|Query
     */
    public function getTaskDynamicBaseQuery(Request $request, string $queryCode, array $param, $pureMode = false)
    {
        switch ($queryCode) {
            case 'in_plan_overdue':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskInPlanOverdueBaseQuery($param);
                break;
            case 'in_plan_not_deal':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskInPlanNotDealBaseQuery($param);
                break;
            case 'in_plan_in_progress':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskInPlanInProgressBaseQuery($param);
                break;
            case 'in_plan_marked_delay':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskInPlanMarkedDelayBaseQuery($param);
                break;
            case 'in_plan_marked_today':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskInPlanMarkedTodayBaseQuery($param);
                break;
            case 'work_order_to_do':
                $queryDbr = TaskStatisticsDynamicService::getInstance()->taskWorkOrderToDoBaseQuery($param);
                break;
            default:
                $queryDbr = true;
        }
        if ($queryDbr instanceof Query) {
            if (!$pureMode) {
                // TODO - 是否全局
                $userIdList = [$request->getSaasUserId()];
                self::taskStepTypeFilter($queryDbr, $param);
                self::taskMyResponsibleFilter($queryDbr, $param, $userIdList, $request->getTenantId());
            }
        }
        return $queryDbr;
    }

    /**
     * @param Request $request
     * @param string $queryCode
     * @param array $param
     * @return array|mixed|Query
     */
    public function getTaskAbnormalBaseQuery(Request $request, string $queryCode, array $param, $pureMode = false)
    {
        switch ($queryCode) {
            case 'upstream_abnormal':
                $queryDbr = TaskStatisticsAbnormalService::getInstance()->taskUpstreamAbnormalBaseQuery($param);
                break;
            case 'upstream_abnormal_not_confirm':
                $queryDbr = TaskStatisticsAbnormalService::getInstance()->taskUpstreamAbnormalBaseQuery($param, 'default');
                break;
            case 'upstream_abnormal_confirmed':
                $queryDbr = TaskStatisticsAbnormalService::getInstance()->taskUpstreamAbnormalBaseQuery($param, 'yes');
                break;
            default:
                $queryDbr = true;
        }
        if (!$pureMode) {
            if ($queryDbr instanceof Query) {
                // TODO - 是否全局
                $userIdList = [$request->getSaasUserId()];
                self::taskStepTypeFilter($queryDbr, $param);
                self::taskMyResponsibleFilter($queryDbr, $param, $userIdList, $request->getTenantId());
            }
        }
        return $queryDbr;
    }

    /**
     * 时间范围内，(按任务)取最新的 `delay_order_task`，只认最后一条
     * @param int|array $projectId
     * @param int $startTime
     * @param int $endTime
     * @param string|array $managerConfirmed
     * @param string|null $type
     * @return string
     */
    public static function getWindowedLastDelayOrderTask(int $startTime = 0, int $endTime = 0, $timeScope = ['original_plan_time']): string
    {
        $dotFrom = " SELECT *, ROW_NUMBER() OVER( PARTITION BY task_id ORDER BY delay_order_task.id desc ) as order_num "
            . " FROM delay_order_task ";
        $dotWhere = [
            'type in ("today_done", "request_delay", "work_order")'
        ];
        $dotTimeWhere = [];
        foreach ($timeScope as $timeCond) {
            $dotTimeWhere[] = " delay_order_task." . $timeCond . " between " . $startTime . " and " . $endTime . " ";
        }
        $dotWhere = implode(" and ", $dotWhere);
        $dotTimeWhere = implode(" or ", $dotTimeWhere);
        return " (SELECT * FROM ( " . $dotFrom . " where " . $dotWhere . " and (" . $dotTimeWhere . ")" . ") as temp_dot where order_num = 1) ";
    }

    /**
     * 计划应完成
     * @param array $param
     * @return Query
     */
    public static function taskPlanDoneBaseQuery(array $param): Query
    {
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', '<>', StatusService::getInstance()->byCode('closed', 'id'));
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        switch ($param['history']) {
            case "solid":
            case "no":
                $dbr = $dbr->whereBetween('task.plan_end_time', [$param['start_time'], $param['end_time']]);
                break;
            case "yes":
            default:
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time']);
                break;
        }
        return $dbr;
    }

    /**
     * 计划内完成
     * @param array $param
     * @return Query
     */
    public static function taskInPlanDoneBaseQuery(array $param): Query
    {
        // 实时: 任务计划完成时间 落在区间 完成
        // 历史: 1. 任务计划完成时间 落在区间 完成
        //      2. 延期单计划完成时间 落在区间 完成
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'final'], false, 'id') . ')');
        // 计划内完成 仅查询完成状态 vs 完成时间限制在查询周期 ?
        // ->whereBetween('task.end_time', [$param['start_time'], $param['end_time']]);
        switch ($param['history']) {
            case "solid":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->join('version', 'version.link_id = task.id and version.module_id = ' . ModuleService::getInstance()->byCode('task', 'id') . ' and version.end_time < version.plan_end_time')
                    ->whereRaw('(task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and task.plan_end_time <= task.plan_end_time)'
                        . ' or version.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']);
                break;
            case "no":
                $dbr = $dbr->whereBetween('task.plan_end_time', [$param['start_time'], $param['end_time']]);
                break;
            case "yes":
            default:
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time']);
                break;
        }
        return $dbr;
    }

    /**
     * 计划外完成
     * @param array $param
     * @return Query
     */
    public static function taskOutsidePlanDoneBaseQuery(array $param): Query
    {
        // 实时 & 历史: 完成时间落在区间，计划完成时间不在区间
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereBetween('task.end_time', [$param['start_time'], $param['end_time']])
            ->whereNotBetween('task.plan_end_time', [$param['start_time'], $param['end_time']])
            ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['final', 'ready_to_publish'], false, 'id') . ')');
        return $dbr;
    }

    /**
     * 实际完成
     * @param array $param
     * @return Query
     */
    public static function taskInAllDoneBaseQuery(array $param): Query
    {
        // 实时 & 历史: 所有完成
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['final', 'ready_to_publish'], false, 'id') . ')')
            ->whereBetween('task.end_time', [$param['start_time'], $param['end_time']]);
        return $dbr;
    }

    /**
     * 计划内未完成
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNotDoneBaseQuery(array $param): Query
    {
        // 实时: 任务计划完成时间 落在区间 完成
        // 历史: 1. 任务计划完成时间 落在区间 未完成
        //      2. 延期单计划完成时间 落在区间 未完成
        // 固化: 1. 任务计划完成时间 落在区间 未完成
        //      2. 延期单计划完成时间 落在区间
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id']);
        switch ($param['history']) {
            case "solid":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->join('version', 'version.link_id = task.id and version.module_id = ' . ModuleService::getInstance()->byCode('task', 'id') . ' and version.end_time < version.plan_end_time')
                    ->whereRaw('(task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and ((get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'closed', 'final'], false, 'id') . ')) or task.plan_end_time < task.end_time))'
                        . ' or (version.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ')');
                break;
            case "yes":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->join('version', 'version.link_id = task.id and version.module_id = ' . ModuleService::getInstance()->byCode('task', 'id'), 'left')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or version.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'closed', 'final'], false, 'id') . ')');
                break;
            case "no":
                $dbr = $dbr->whereBetween('task.plan_end_time', [$param['start_time'], $param['end_time']])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'closed', 'final'], false, 'id') . ')');
                break;
            default:
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['ready_to_publish', 'closed', 'final'], false, 'id') . ')');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内未处理: 未申请 + 等待处理 + 未通过
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNotDealBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type = "today_done" or dot.manager_confirmed in ("yes", "default"))', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                // 历史计划 来自延期单
                // 延期状态取最后一条延期单
                // 未处理 无延期单 或 (非标记今日 且 未确认延期)
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "no")')
                    ->whereRaw('dot.id is null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内 未处理
     * 无进行中工单，无通过延期单
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNeedSolutionDoneBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            // 关联最后一条延期单 需要延期并通过
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "yes")', 'left')
            // 关联进行中工单
            ->join(['task_work_order' => 'two'], 'two.task_id = task.id and two.status in ( ' . TaskWorkOrderModel::STATUS_UNTREATED . ',' . TaskWorkOrderModel::STATUS_PROGRESS . ')', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null and two.id is null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null and two.id is null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内已申请，等待中
     * @param array $param
     * @return Query
     */
    public static function taskInPlanPendingBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            // 关联最后一条延期单 需要延期并且未处理
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "default")', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "yes")')
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内已申请，已通过
     * @param array $param
     * @return Query
     */
    public static function taskInPlanMarkedBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type = "today_done" or dot.manager_confirmed = "yes")', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成，标记今日
     * @param array $param
     * @return Query
     */
    public static function taskInPlanMarkedTodayBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and dot.type = "today_done"', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "yes")')
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_end_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内逾期
     * @param array $param
     * @return Query
     */
    public static function taskInPlanOverdueBaseQuery(array $param): Query
    {
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id']);
        $statusSql = empty($param['task_status'])
            ? StatusService::getInstance()->byCodeMap(['ready_to_publish', 'closed', 'final'], true, 'id')
            : StatusService::getInstance()->byCodeMap($param['task_status'], false, 'id');
        switch ($param['history']) {
            case "solid":
            case "yes":
            default:

                $dbr = $dbr->whereBetween('task.plan_end_time', [$param['start_time'], $param['end_time']])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . $statusSql . ')');
                break;
        }
        return $dbr;
    }

    /**
     * 计划完成内延期
     * @param array $param
     * @return Query
     */
    public static function taskInPlanDelayBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['original_plan_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            // 关联最后一条延期单 需要延期并确认
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "yes")', 'left')
            ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'ready_to_publish', 'final'], false, 'id') . ')')
            ->whereRaw('dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time']);
        return $dbr;
    }

    /**
     * 计划应交付
     * @param array $param
     * @return Query
     */
    public static function taskPlanDeliveryBaseQuery(array $param): Query
    {
        // 实时: 任务计划交付时间
        // 过去: 任务计划交付时间 + （全部通过延期）原计划交付时间
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', '<>', StatusService::getInstance()->byCode('closed', 'id'));
        switch ($param['history']) {
            case "node_yes":
            case "node_no":
            case "node_default":
                break;
            case "yes":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.id is not null and dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ')');
                break;
            default:
                $dbr = $dbr->whereBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']]);
                break;
        }
        return $dbr;
    }

    /**
     * 计划内交付
     * @param array $param
     * @return Query
     */
    public static function taskInPlanDeliveryBaseQuery(array $param)
    {
        // 实时: 任务计划交付时间 落在区间 交付
        // 历史: 1. 任务计划交付时间 落在区间 交付
        //      2. 延期单计划交付时间 落在区间 交付
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id']);
//            ->whereBetween('task.delivery_time', [$param['start_time'], $param['end_time']]);
        switch ($param['history']) {
            case "node_yes":
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'));
                break;
            case "yes":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']);
                break;
            default:
                $dbr = $dbr->whereBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']])
                    ->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'));
                break;
        }
        return $dbr;
    }

    /**
     * 计划外交付
     * @param array $param
     * @return Query
     */
    public static function taskOutsidePlanDeliveryBaseQuery(array $param): Query
    {
        // 实时 & 历史: 交付时间落在区间，计划交付时间不在区间
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereBetween('task.delivery_time', [$param['start_time'], $param['end_time']])
            ->whereNotBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']])
            ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['final'], false, 'id') . ')');
        return $dbr;
    }

    /**
     * 实际交付
     * @param array $param
     * @return Query
     */
    public static function taskInAllDeliveryBaseQuery(array $param): Query
    {
        // 全部交付: 计划内 + 计划外
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->whereFieldRaw('get_json_string(task.`json`, "$.task_status")', StatusService::getInstance()->byCode('final', 'id'))
            ->whereBetween('task.delivery_time', [$param['start_time'], $param['end_time']]);
        return $dbr;
    }

    /**
     * 计划内未交付
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNotDeliveryBaseQuery(array $param): Query
    {
        // 实时: 任务计划交付时间 and 任务状态 not closed/final
        // 过去: 任务计划交付时间 and （全部通过延期）原计划交付时间 and 任务状态 not closed/final or 存在 < 任务实际交付时间
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id']);
        switch ($param['history']) {
            case "node_yes":
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')');
                break;
            case "solid":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('(task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and ((get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')) or task.plan_delivery_time < task.delivery_time))'
                        . ' or ((dot.type = "today_done" or dot.manager_confirmed = "yes") and dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and (dot.plan_delivery_time = 0 or dot.plan_delivery_time < task.end_time))');
                break;
            case "yes":
                $dbr = $dbr->join(['delay_order_task' => 'dot'], 'dot.task_id = task.id and dot.manager_confirmed = "yes"', 'left')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or ((dot.type = "today_done" or dot.manager_confirmed = "yes") and dot.original_plan_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ')')
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')');
                break;
            default:
                $dbr = $dbr->whereBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')');
        }
        return $dbr;
    }

    /**
     * 计划交付内，未处理 + 等待处理 + 不通过
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNotDealDeliveryBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type = "today_done" or dot.manager_confirmed in ("yes", "default"))', 'left');
        switch ($param['history']) {
            case "node_yes":
                $dbr = $dbr->whereRaw('dot.id is null');
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')')
                    ->whereRaw('dot.id is null');
                break;
            case "solid":
            case "yes":
                // 历史计划 来自延期单
                // 延期状态取最后一条延期单
                // 未处理 无延期单 或 (非标记今日 且 未确认延期)
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "no")')
                    ->whereRaw('dot.id is null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划交付内 未处理
     * 无进行中工单，无通过延期单
     * @param array $param
     * @return Query
     */
    public static function taskInPlanNeedSolutionDeliveryBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            // 关联最后一条延期单 需要延期并通过
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "yes")', 'left')
            // 关联进行中工单
            ->join(['task_work_order' => 'two'], 'two.task_id = task.id and two.status in ( ' . TaskWorkOrderModel::STATUS_UNTREATED . ',' . TaskWorkOrderModel::STATUS_PROGRESS . ')', 'left');
        switch ($param['history']) {
            case "solid":
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null and two.id is null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is null and two.id is null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划交付内，已申请，等待中
     * @param array $param
     * @return Query
     */
    public static function taskInPlanPendingDeliveryBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            // 关联最后一条延期单 需要延期并且未处理
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "default")', 'left');
        switch ($param['history']) {
            case "node_yes":
                $dbr = $dbr->whereRaw('dot.id is not null');
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')')
                    ->whereRaw('dot.id is not null');
                break;
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "yes")')
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 已处理: 计划交付内，标记今日 + 已通过延期
     * @param array $param
     * @return Query
     */
    public static function taskInPlanMarkedDeliveryBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and (dot.type = "today_done" or dot.manager_confirmed = "yes")', 'left');
        switch ($param['history']) {
            case "node_yes":
                $dbr = $dbr->whereRaw('dot.id is not null');
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')')
                    ->whereRaw('dot.id is not null');
                break;
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 计划交付内，标记今日
     * @param array $param
     * @return Query
     */
    public static function taskInPlanMarkedTodayDeliveryBaseQuery(array $param): Query
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and dot.type = "today_done"', 'left');
        switch ($param['history']) {
            case "node_yes":
                $dbr = $dbr->whereRaw('dot.id is not null');
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')')
                    ->whereRaw('dot.id is not null');
                break;
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or (dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'] . ' and dot.manager_confirmed = "yes")')
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }

    /**
     * 逾期: 当天计划交付内逾期
     * @param array $param
     * @return Query
     */
    public static function taskInPlanOverdueDeliveryBaseQuery(array $param): Query
    {
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id']);
        $statusSql = empty($param['task_status'])
            ? StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id')
            : StatusService::getInstance()->byCodeMap($param['task_status'], false, 'id');
        switch ($param['history']) {
            case "solid":
            case "yes":
            default:

                $dbr = $dbr->whereBetween('task.plan_delivery_time', [$param['start_time'], $param['end_time']])
                    ->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . $statusSql . ')');
                break;
        }
        return $dbr;
    }

    /**
     * 延期: 当天计划交付内，延期已处理
     * @param array $param
     * @return Query
     */
    public static function taskInPlanDelayDeliveryBaseQuery(array $param)
    {
        $dotLastJoin = self::getWindowedLastDelayOrderTask($param['start_time'], $param['end_time'], ['plan_delivery_time']);
        $dbr = TaskModel::whereIn('task.project_id', $param['project_id'])
            ->join([$dotLastJoin => 'dot'], 'dot.task_id = task.id and dot.type in ("request_delay", "work_order") and dot.manager_confirmed = "yes"', 'left');
        // 关联最后一条延期单 需要延期并确认
        switch ($param['history']) {
            case "node_yes":
                $dbr = $dbr->whereRaw('dot.id is not null');
                break;
            case "node_no":
            case "node_default":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], true, 'id') . ')')
                    ->whereRaw('dot.id is not null');
                break;
            case "yes":
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time']
                        . ' or dot.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
            default:
                $dbr = $dbr->whereRaw('get_json_string(task.`json`, "$.task_status") not in (' . StatusService::getInstance()->byCodeMap(['closed', 'final'], false, 'id') . ')')
                    ->whereRaw('task.plan_delivery_time between ' . $param['start_time'] . ' and ' . $param['end_time'])
                    ->whereRaw('dot.id is not null');
                break;
        }
        return $dbr;
    }
}
